﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using XuanFe.Database.Model;

namespace XuanFe.Database.DAL
{
    public class SequenceDAL
    {
        private const string SQL_SELECT_WA_SEQUENCES = "select SEQ_NAME,SEQ_MIN_VALUE,SEQ_MAX_VALUE,SEQ_NEXT_NUMBER,SEQ_INCREMENT_BY,SEQ_CYCLE_FLG from WS_SEQUENCES where 1=1";
        private const string SQL_INSERT_WS_SEQUENCES = "insert into WS_SEQUENCES(SEQ_NAME,SEQ_MIN_VALUE,SEQ_MAX_VALUE,SEQ_NEXT_NUMBER,SEQ_INCREMENT_BY,SEQ_CYCLE_FLG) values(@Name,@MinValue,@MaxValue,@NextNumber,@IncrementBy,@CycleFlag)";
        private const string SQL_UPDATE_WS_SEQUENCES = "update WS_SEQUENCES set SEQ_NEXT_NUMBER=@NextNumber where 1=1";
        private const string SQL_LOCK_SEQUENCE = "update WS_SEQUENCES set SEQ_NEXT_NUMBER=SEQ_NEXT_NUMBER where 1=1";

        private const string SQL_WHERE_NAME = " and SEQ_NAME=@Name ";

        private const string PARM_NAME = "@Name";
        private const string PARM_MIN_VALUE = "@MinValue";
        private const string PARM_MAX_VALUE = "@MaxValue";
        private const string PARM_NEXT_NUMBER = "@NextNumber";
        private const string PARM_INCREMENT_BY = "@IncrementBy";
        private const string PARM_CYCLE_FLAG = "@CycleFlag";

        private static SequenceDAL instance = new SequenceDAL();
        /// <summary>
        /// 初始化实例
        /// </summary>
        /// <returns></returns>
        public static SequenceDAL Instance
        {
            get
            {
                return instance;
            }
        }

        public SequenceModel Get(DbTransaction trans, string name)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_SELECT_WA_SEQUENCES);
            sbSQL.Append(SQL_WHERE_NAME);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = name });

            using (DataSet ds = MySQLHelper.Instance.ExecuteDataSet(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray()))
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    DataRow t = ds.Tables[0].Rows[0];
                    return new SequenceModel()
                    {
                        Name = Convert.ToString(t.ItemArray[0]),
                        MinValue = Convert.ToDecimal(t.ItemArray[1]),
                        MaxValue = Convert.ToDecimal(t.ItemArray[2]),
                        NextNumber = Convert.ToDecimal(t.ItemArray[3]),
                        IncrementBy = Convert.ToDecimal(t.ItemArray[4]),
                        Cycle = string.Equals(Convert.ToString(t.ItemArray[5]), "Y")
                    };
                }
                return null;
            }
        }
        public int Insert(DbTransaction trans, SequenceModel model)
        {
            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Name });
            parms.Add(new MySqlParameter(PARM_MIN_VALUE, MySqlDbType.Decimal) { Direction = ParameterDirection.Input, Value = model.MinValue });
            parms.Add(new MySqlParameter(PARM_MAX_VALUE, MySqlDbType.Decimal) { Direction = ParameterDirection.Input, Value = model.MaxValue });
            parms.Add(new MySqlParameter(PARM_NEXT_NUMBER, MySqlDbType.Decimal) { Direction = ParameterDirection.Input, Value = model.NextNumber });
            parms.Add(new MySqlParameter(PARM_INCREMENT_BY, MySqlDbType.Decimal) { Direction = ParameterDirection.Input, Value = model.IncrementBy });
            parms.Add(new MySqlParameter(PARM_CYCLE_FLAG, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Cycle ? "Y" : "N" });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, SQL_INSERT_WS_SEQUENCES, parms.ToArray());
        }
        public int Update(DbTransaction trans, SequenceModel model)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_UPDATE_WS_SEQUENCES);
            sbSQL.Append(SQL_WHERE_NAME);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Name });
            parms.Add(new MySqlParameter(PARM_NEXT_NUMBER, MySqlDbType.Decimal) { Direction = ParameterDirection.Input, Value = model.NextNumber });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        /// <summary>
        /// 锁定数据库序列
        /// </summary>
        /// <param name="trans">数据库事务</param>
        /// <param name="name">序列名</param>
        /// <returns></returns>
        public int Lock(DbTransaction trans, string name)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_LOCK_SEQUENCE);
            sbSQL.Append(SQL_WHERE_NAME);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = name });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
    }
}
